#coding:utf-8
import sys
sys.path.append("..")

from MysqlCon import MysqlCon
import time


class NewestFTX(object):
    """
    新房-房天下 数据的导入

    1、读取文件数据
    2、写入库表
    """


    #多行插入
    def insert_many(self, connect, cursor, insert_data):
        # 获取数据，写入数据库 %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
        insert_sql = """
        insert into ori_newest_info_ftx_20210105(
            platform, url, newest_name, city_name, county_name, block_name, ori_lnglat, address, sale_address, sale_phone, alias, former_name, layout, total_price, unit_price, recent_opening_time, resent_delivery_time, issue_date, issue_number, developer, investor, brander, land_area, building_area, arch_style, green_rate, volume_rate, building_type, property_tyoe, household_num, right_term, property_comp, property_fee, park_rate, park_num, park_price, orientation, building_num, avg_distance, ong_park_num, ung_park_num, build_density, floor_num, ambitus, bike_park_number, agent, decoration, layout_pic, estate_pic, advantage, disadvantages, pay_meth, sale_status, gd_city, gd_county, gd_lnglat, gd_office_name, is_accurate
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s)
        """
        connect.ping(reconnect = True)

        # 批量插入
        try:
            res = cursor.executemany(insert_sql, insert_data)
            print("res = ", res)
            # 提交，不进行提交无法保存到数据库
            connect.commit()
        except Exception as e:
            print(e)
            connect.rollback()
        #finally:
        #connect.close()

        return True


    #生成行数据列表
    def makeDataRow(self, line, nowNum):
        #分解列值
        cols = line.split("\t")
        colNum = len(cols)
        if colNum < 58 :
            #print(nowNum, " 列数：", colNum)
            #print(cols)
            nv = ['', '', '', '', '']
            cols.extend(nv)
            colNum = len(cols)

        rowData = []
        i = 0
        while i < colNum :
            rowData.append(cols[i])
            i += 1
        return rowData



if __name__ == "__main__":

    # 获取数据库连接
    mysql_con = MysqlCon()
    connect_odsdb, cursor_odsdb = mysql_con.con_odsdb()

    #记录执行开始时间
    str_time = int(time.time())

    #实例化类
    newest = NewestFTX()

    # 读取原文件
    with open('D:\\usr\\EJU\\数据部门\\大数据系统\\商业客户数据部\\数据迁移\\待入库文件20210111\\新房-房天下.txt','r',encoding='utf-8') as f:
        lines = f.readlines()
        #print(text)
    #跳过第一行表头
    header = lines.pop(0)
    print(header)

    maxRow = len(lines)
    print("总记录数", maxRow)

    row = 0
    insertDataList = []
    for line in lines:
        #分解列值
        row = row + 1
        rowData = newest.makeDataRow(line, row)
        #continue
        insertDataList.append(rowData)
        if row > 0 and (row % 1000 == 0 or row == maxRow):
            print(row)
            # 批量写入1000条数据
            newest.insert_many(connect_odsdb, cursor_odsdb, insertDataList)
            insertDataList.clear()

    connect_odsdb.close()

    end_time = int(time.time())
    print("程序执行时间（秒）：", end_time - str_time)
